﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using DAL;
using CommontLibrary;
using ExportToExcel;
using System.Text;
using System.IO;


public partial class UserControl_Role_InportExportExcel : System.Web.UI.UserControl
{
    private string transform_string_vi_date_2_en_date(string ip_str_vi_date)
    {
        string v_str_en_date = "";
        string[] v_str_vi_date_array = ip_str_vi_date.Split('/');
        v_str_en_date += v_str_vi_date_array[1] + "/" + v_str_vi_date_array[0] + "/" + v_str_vi_date_array[2];
        return v_str_en_date;
    }

    #region "Member variables"
    private int intUserID = 0;
    private int intHocVienID = 0;
    private int intModuleID = 0;
    bool boolRead = false;
    bool boolWrite = false;
    bool boolUpdate = false;
    bool boolDelete = false;
    public string m_str_error_loi_trung_contact = "";
    public int v_i_check = 0;
    public string m_str_error_loi_trong_sdt = "";
    DAHocVien objHocVien;
    DANguoidung objNguoidung;
    DANguoiTimNguon objNguoiTimNguon;
    DATrangThaiGoi objTrangThaiGoi;
    DANguon objNguon;
    DALog objLog;
    int intLevelID = 1;
    #endregion
    protected void Page_Load(object sender, EventArgs e)
    {
        loadDataAccess();
        if (!Page.IsPostBack)
        {
            this.lblFileExportExcel.Text = "Contact" + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + ".xls";
        }
        LoadDataForDDL();
    }
    private void LoadDataForDDL()
    {
        SqlConnection sqlConnec = ConnectData.getConnection();
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT A.ID,A.HoTen FROM TB_NguoiTimNguon A INNER JOIN TB_Nguon B ON A.NguonID=B.ID";
        command.CommandType = CommandType.Text;
        command.Connection = sqlConnec;
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = command;
        DataTable dt = new DataTable();
        da.Fill(dt);
        ddlNguoiTimNguon.DataSource = dt.DefaultView;
        ddlNguoiTimNguon.DataBind();
        //ddlNguoiTimNguon.DataTextField = "HoTen";
        //ddlNguoiTimNguon.DataValueField = "ID";

    }
    private void loadDataAccess()
    {
        SqlConnection sqlConnec = ConnectData.getConnection();
        objHocVien = new DAHocVien(sqlConnec);
        objNguoidung = new DANguoidung(sqlConnec);
        objNguon = new DANguon(sqlConnec);
        objNguoiTimNguon = new DANguoiTimNguon(sqlConnec);
        objTrangThaiGoi = new DATrangThaiGoi(sqlConnec);
        objLog = new DALog(sqlConnec);
    }

    // Đây là quá trình đọc dữ liệu từ file excel và insert vào trong csdl
    protected void btnInportExcel_Click(object sender, EventArgs e)
    {
        string v_str_script;
        SqlConnection sqlConnec = ConnectData.getConnection();
        String filename = fileInportExcel.FileName.ToString();
        string v_str_file_without_extension;
        string[] v_str_split_filename = filename.Split('.');
        v_str_file_without_extension = "";
        for (int v_i = 0; v_i < v_str_split_filename.Length - 1; v_i++)
        {
            v_str_file_without_extension += v_str_split_filename[v_i];
        }
        string v_str_extension = v_str_split_filename[v_str_split_filename.Length - 1];
        filename = v_str_file_without_extension.Trim() + "v" + DateTime.Now.Year + "." + DateTime.Now.Month + "." + DateTime.Now.Day + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute;
        filename += "." + v_str_extension;
        //FileInfo v_file_info = new FileInfo(MapPath("~/OutputFiles/" + filename));
        //if (v_file_info.Exists) File.Delete(MapPath("~/OutputFiles/" + filename));
        ExcelHelper.uploadXLFileWithFileName(fileInportExcel, Server.MapPath("~/OutputFiles/"), filename);
        String[] sheetNames = new String[100];
        sheetNames = ExcelHelper.getExcelSheets(Server.MapPath("~/OutputFiles/") + filename);
        // loop through worksheet names
        foreach (string shtname in sheetNames)
        {
            DataTable dt = null;
            if (shtname.Equals("CĐ Khác"))
            {
                dt = ExcelHelper.getXLDataCRM(shtname, Server.MapPath("~/OutputFiles/") + filename, " ", true, Int32.Parse(ddlNguoiTimNguon.SelectedValue));
                try
                {
                    InsertRow(dt);
                }
                catch (Exception v_e)
                {
                    v_str_script = string.Format("<script type='text/javascript'>alert('Đã xảy ra lỗi trong quá trình import. \n {0}')</script>", v_e.Message);
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "onError", v_str_script);
                    return;
                }
            }
            else
            {
                string v_fileName = Server.MapPath("~/OutputFiles/") + filename;
                dt = ExcelHelper.getXLDataCRM(shtname, v_fileName, " ", false, Int32.Parse(ddlNguoiTimNguon.SelectedValue));
                try
                {
                    InsertRow(dt);
                }
                catch (Exception v_e)
                {
                    v_str_script = string.Format("<script type='text/javascript'>alert('Đã xảy ra lỗi trong quá trình import. \n {0}')</script>", v_e.Message);
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "onError", v_str_script);
                    return;
                }
            }

            //BulkCopy
            //ExcelHelper.BulkCopy(ds.Tables[0], sqlConnec, "TB_HocVien");
        }
        Session["UserID"] = intUserID;
        objLog.insertLog(0, 0, 14, intUserID);
        v_str_script = "<script language='javascript'>alert('Import dữ liệu thành công')</script>";
        Page.ClientScript.RegisterStartupScript(this.GetType(), "onsucess", v_str_script);
    }

    // Thực hiện việc chèn dữ liệu từ DataTable vào trong CSDL
    public void InsertRow(DataTable dt)
    {
        string v_str_error = "";
        decimal v_dc_nguoi_dung_id = Convert.ToDecimal(Session["UserID"].ToString());
        foreach (DataRow dr in dt.Rows)
        {
            string v_str_check_trung = "";
            string strMaHocVien = dr["MaHocVien"].ToString();
            string strDiaChiThu = dr["DiaChiThu"].ToString(); // Nơi sống
            string strHoTen = dr["HoTen"].ToString();
            string strDienThoai = dr["DienThoai"].ToString();
            if (strDienThoai.Equals(""))
            {
                m_str_error_loi_trong_sdt += strHoTen + ",";
                continue;
            }
            v_str_check_trung = objHocVien.checkSoDienThoaiHocVien(strDienThoai);
            if (v_str_check_trung != "")
            {
                m_str_error_loi_trung_contact += strHoTen + " SĐT: " + strDienThoai + ",";
                v_i_check++;
                continue;
            }
            string strDiLam = dr["DiLam"].ToString();  // đã tốt nghiệp trưởng nào?
            string strEmail = dr["Email"].ToString();  // Email
            
            //DateTime strNgaySinh = DateTimeUntil.ConvertDateCulture((dr["NgaySinh"].ToString()), "en-US");
            //DateTime strNgayDangKy = DateTimeUntil.ConvertDateCulture((dr["NgayDangKy"].ToString()), "en-US");
            //string v_str_ngay_sinh = transform_string_vi_date_2_en_date(Convert.ToString(dr["NgaySinh"]));
            //string v_str_ngay_dang_ky = transform_string_vi_date_2_en_date(Convert.ToString(dr["NgayDangKy"]));
            //DateTime strNgaySinh;
            //DateTime strNgayDangKy;
            //strNgaySinh = DateTimeUntil.ConvertDateCulture(v_str_ngay_sinh, "en-US");
            //strNgayDangKy = DateTimeUntil.ConvertDateCulture(v_str_ngay_dang_ky, "en-US");

            string striGhiChuNguon = dr["GhiChuNguon"].ToString(); ;
            int intLevelID = 1;
            //bool boolGioiTinh = Convert.ToBoolean(dr["GioiTinh"]);
            int intNguonID = Convert.ToInt32(dr["NguonID"]);
            //int intTruongID = 0;
            //intTruongID = Convert.ToInt32(dr["TruongID"]);
            int intNguoiTimNguonID = Int32.Parse(ddlNguoiTimNguon.SelectedValue);
            int intChẹck = objHocVien.importHocVien(strHoTen, strDienThoai, strDiaChiThu,
                strEmail, strDiLam, int.Parse(Session["UserID"].ToString()), strMaHocVien, intNguoiTimNguonID,
                striGhiChuNguon, intLevelID);
            if (intChẹck < 0)
            {
                v_str_error += dr["ID"].ToString();
            }
        }
        this.lblMess.Text = this.lblMess.Text + "<BR>" + v_str_error;
    }

    protected void btnExportExcel_Click(object sender, EventArgs e)
    {
        //string strSqlSelect = "Select * FROM TB_HocVien ORDER BY MaHocVien";
        //string outputname = Server.MapPath("~/OutputFiles/") + this.lblFileExportExcel.Text ;
        //SqlConnection sqlConnec = ConnectData.getConnection();
        //ExcelHelper.ImportToMultipleXLSheets(strSqlSelect, outputname, sqlConnec);
        export_excel_file();
        Session["UserID"] = intUserID;
        objLog.insertLog(0, 0, 15, intUserID);
    }
    private DataTable get_data_2_DataTable(int nguoidungid)
    {
        DataTable v_dt_table = new DataTable();
        v_dt_table = objHocVien.load_all_hoc_vien(nguoidungid);
        return v_dt_table;
    }
    private void export_excel_file()
    {
        int nguoidungid = int.Parse(Session["UserID"].ToString());
        HttpContext context = HttpContext.Current;
        DataTable dt = get_data_2_DataTable(nguoidungid);
        string attachment = "attachment; filename=Contact_Ungvien.v" + DateTime.Now.Year + "." + DateTime.Now.Month + "." + DateTime.Now.Day + ".xls";
        context.Response.ClearContent();
        context.Response.AddHeader("content-disposition", attachment);
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = System.Text.Encoding.Unicode;
        context.Response.BinaryWrite(Encoding.Unicode.GetPreamble());
        string tab = "";
        foreach (DataColumn dc in dt.Columns)
        {
            context.Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        context.Response.Write("\n");

        int i;
        foreach (DataRow dr in dt.Rows)
        {
            tab = "";
            for (i = 0; i < dt.Columns.Count; i++)
            {
                context.Response.Write(tab + dr[i].ToString());
                tab = "\t";
            }
            context.Response.Write("\n");
        }
        context.Response.End();
    }
}
